package com.dhcc.DRGsHosp.facade.impl;

import com.dhcc.DRGsHosp.application.DrgsIndexCalApplication;
import com.dhcc.DRGsHosp.application.PayGroupApplication;
import com.dhcc.DRGsHosp.facade.DrgsIndexCalFacade;
import com.dhcc.DRGsHosp.facade.PayGroupFacade;
import com.dhcc.DRGsHosp.facade.dto.IndexCalDTO;
import com.dhcc.DRGsHosp.facade.impl.assembler.IndexCalAssembler;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;

import javax.inject.Named;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;



@Named("drgsHospFacadeImpl20170822chl01")
public class PayGroupFacadeImpl implements PayGroupFacade {
	
	@Autowired
	private PayGroupApplication payGroupApplication;
	@Autowired
	private DrgsIndexCalFacade drgsIndexCalFacade;
	@Autowired
	private DrgsIndexCalApplication drgsIndexCalApplication ;
	
	/**
	 * 获取最小值  最大值  中位数
	 *  @Description：
	 *  @Author ChenHanlin  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#findFeeExtremal()
	 */
	@Override
	public List<Map<String, Object>> findFeeExtremal(IndexCalDTO dto) {
		//将时间格式化
	    dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    dto.setVerRegion(drgsIndexCalFacade.getGroupScheme());
		StringBuffer strb = new StringBuffer() ;
		strb.append(" select min(ipinfo.p00800000) minval, max(ipinfo.p00800000) maxval,median(ipinfo.p00800000) mediancal  \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and pd.pardrg_code = :pardrgCode and 1=1  \n ") ;
		strb.append(" order by ipinfo.p00800000  \n ") ;
		String sql = strb.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 and 1=1 \n " ) ;
		}
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " medins.parid is not null \n  and 1=1 " ) ;
		}
		List<Map<String, Object>>  mapList = payGroupApplication.executeQueryForList(sql, dto) ;
		return mapList;
	}
	
	/**
	 *  更新付费病组
	 *  @Description：
	 *  @Author ChenHanlin  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#updatePayFee(java.lang.String, java.lang.String)
	 */
	@Override
	public boolean updatePayFee(IndexCalDTO dto, String action) {
		String dateFrom = DrgsIndex.formateDateFrom(dto.getDateFrom());
		String dateTo = DrgsIndex.formateDateTo(dto.getDateTo());
		String verCode = drgsIndexCalFacade.getVerCode(dateFrom, dateTo);
		String sql = "update drgs.t_drgs drg set drg.pay_group = :action where drg.drg_code = :drgCode and drg.ver_code = :verCode" ;
		 Map<String,Object> condition = new HashMap<>() ;
		 condition.put("drgCode", dto.getPardrgCode()) ;
		 condition.put("action", action) ;
		 condition.put("verCode", verCode) ;
		int count = payGroupApplication.executeUpdate(sql, condition) ;
		if(count>=1) {
			return true;
		}
		return false;
	}
	/**
	 * 
	 *  @Description：
	 *  @Author ChenHanlin  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#queryCostStructureBenchmarking(com.dhcc.DRGsHosp.facade.dto.IndexCalDTO, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> queryCostStructureBenchmarking(IndexCalDTO dto, String sliderVal) {
		// 时间格式化
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	   //获取分组方案
	    dto.setVerRegion(drgsIndexCalFacade.getGroupScheme());
	    dto.setMsCode("std_paygroup");
	    dto.setManageYear(drgsIndexCalFacade.getManageYear(dto));
		StringBuffer strb = new StringBuffer() ;
		// 区间病例
		strb.append(" select lv.lv intervel,count(intervel) incnt,ROUND(AVG(P00800000), 2) AVGFEE ,ROUND(AVG(AVGDAYS), 0) AVGDAYS, ROUND(AVG(a.drugcost), 2) drugcost, \n ") ;
		strb.append(" ROUND(AVG(a.manage_benchmark), 2) AVGFEEStd , 0 minVal , 0 maxVal,   \n ") ;
		strb.append(" ROUND(AVG(a.medfee), 2) medfee,ROUND(AVG(a.nursfee), 2) nursfee,ROUND(AVG(a.techfee), 2) techfee,ROUND(AVG(a.drugMaterFee), 2) drugMaterFee,  \n ") ;
		strb.append(" ROUND(AVG(a.managefee), 2) managefee from   \n ") ;
		strb.append(" (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 3) lv \n ") ;
		strb.append(" left join (select  case when ipinfo.p00800000 > "+sliderVal+"*mdtl.manage_benchmark then 1 \n ") ;
		strb.append(" when ipinfo.p00800000 > mdtl.manage_benchmark and ipinfo.p00800000 <= "+sliderVal+"*mdtl.manage_benchmark  then 2 \n ") ;
		strb.append(" when ipinfo.p00800000 <= mdtl.manage_benchmark then 3 end  intervel , \n ") ;
		strb.append(" ipinfo.p00800000,mdtl.manage_benchmark ,ipinfo.P00320000 avgdays,  \n ") ;
		strb.append(" expsum.drg_med_cost medfee,expsum.drg_nurs_cost nursfee,expsum.drg_tech_cost techfee, \n ") ;
		strb.append(" expsum.drg_drug_cost drugMaterFee,expsum.drg_manage_cost managefee,expsum.drug_cost drugcost \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
		strb.append(" and pd.pardrg_code = :pardrgCode   \n ") ;
		strb.append(" )a on a.intervel = lv.lv \n ") ;
		strb.append(" group by lv.lv \n ") ;
		strb.append(" union \n ") ;
		// 全部病例
		strb.append(" select 0 intervel, count(1) incnt,ROUND(AVG(P00800000), 2) AVGFEE,ROUND(AVG(ipinfo.P00320000), 2) avgdays,ROUND(AVG(expsum.drug_cost), 2) drugcost,ROUND(AVG(mdtl.manage_benchmark), 2) AVGFEEStd,min(P00800000) minVal,max(P00800000) maxVal, \n ") ;
		strb.append(" ROUND(AVG(expsum.drg_med_cost),2) medfee,ROUND(AVG(expsum.drg_nurs_cost),2) nursfee,ROUND(AVG(expsum.drg_tech_cost),2) techfee, \n ") ;
		strb.append(" ROUND(AVG(expsum.drg_drug_cost),2) drugMaterFee,ROUND(AVG(expsum.drg_manage_cost),2) managefee  \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and pd.ver_region=:verRegion  \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
		strb.append(" and pd.pardrg_code = :pardrgCode  \n ") ;
		// 标杆值
		strb.append(" union  \n ") ;
		strb.append(" select 4 intervel, 0 incnt,std.avgFee AVGFEE,std.AvgDays  avgdays,std.DrugFee drugcost, std.avgFee AVGFEEStd ,0 minVal,0 maxVal, \n ") ;
		strb.append(" std.med medfee,std.nurs nursfee,std.tech techfee,std.DrugMaterFee DrugMaterFee, std.manage managefee \n ") ;
		strb.append(" from ( \n ") ;
		strb.append(" select * from (select parquota_code, manage_benchmark from drgs.t_managesummarydtl mdtl \n ") ;
		strb.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.std_year = :manageYear and mdtl.patient_type = '1' and mdtl.pardrg_code = :pardrgCode ) \n ") ;
		strb.append(" pivot (sum(manage_benchmark) for parquota_code \n ") ;
		strb.append(" in ('AvgFee' avgFee , 'AvgDays' AvgDays , 'MedFee' med, 'NursFee' nurs, 'TechFee' tech, 'ManageFee' manage, 'DrugMaterFee' DrugMaterFee,'DrugFee' DrugFee )) \n ") ;
		strb.append(" )std \n ") ;
		String sql = strb.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 and 1=1 \n " ) ;
		}
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " medins.parid is not null \n  and 1=1 " ) ;
		}
		List<Map<String, Object>>  mapList = payGroupApplication.executeQueryForList(sql, dto) ;
		return mapList;
	}
	
	/**
	 *  查询标差法法费用构成
	 *  @Description：
	 *  @Author ChenHanlin  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#queryCostStructureStdDev(com.dhcc.DRGsHosp.facade.dto.IndexCalDTO, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> queryCostStructureStdDev(IndexCalDTO dto, String sliderVal) {
		// 时间格式化
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    //获取分组方案 and  pd.ver_region=:verRegion
	    dto.setVerRegion(drgsIndexCalFacade.getGroupScheme());
	    dto.setMsCode("std_paygroup");
	    dto.setManageYear(drgsIndexCalFacade.getManageYear(dto));
		StringBuffer strb = new StringBuffer() ;
		// 区间病例
		strb.append(" select lv.lv intervel,count(intervel) incnt,ROUND(AVG(P00800000), 2) AVGFEE,ROUND(AVG(AVGDAYS), 0) AVGDAYS,ROUND(AVG(a.drugcost), 2) drugcost,ROUND(AVG(a.manage_benchmark), 2) AVGFEEStd, 0 minVal , 0 maxVal ,  \n ") ;
		strb.append(" ROUND(AVG(a.med), 2) med,ROUND(AVG(a.nurs), 2) nurs,ROUND(AVG(a.tech), 2) tech,ROUND(AVG(a.drug), 2) drug,  \n ") ;
		strb.append(" ROUND(AVG(a.tech), 2)tech from   \n ") ;
		strb.append(" (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 3) lv \n ") ;
		strb.append(" left join (select  case when ipinfo.p00800000 > "+sliderVal+"*stddevia.stddevia then 1 \n ") ;
		strb.append(" when ipinfo.p00800000 <= "+sliderVal+"*stddevia.stddevia and ipinfo.p00800000 > stddevia.stddevia  then 2 \n ") ;
		strb.append(" when ipinfo.p00800000 <= stddevia.stddevia then 3 end  intervel , \n ") ;
		strb.append(" ipinfo.p00800000,mdtl.manage_benchmark ,ipinfo.P00320000 avgdays, \n ") ;
		strb.append(" expsum.drg_med_cost med,expsum.drg_nurs_cost nurs,expsum.drg_tech_cost tech, \n ") ;
		strb.append(" expsum.drg_drug_cost drug,expsum.drg_manage_cost manage,expsum.drug_cost drugcost  \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code \n ") ;
		strb.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" left join (select  stddev(ipinfo.p00800000) stddevia \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and pd.pardrg_code = :pardrgCode and 1=1 )stddevia on 2=2 \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo  and drg.drg_code = :pardrgCode and 1=1 \n ") ;
		strb.append(" )a on a.intervel = lv.lv \n ") ;
		strb.append(" group by  lv.lv \n ") ;
		// 全部病例
		strb.append(" union \n ") ;
		strb.append(" select 0 intervel, count(1) incnt,ROUND(AVG(P00800000), 2) AVGFEE,ROUND(AVG(ipinfo.P00320000), 0) AVGDAYS,ROUND(AVG(expsum.drug_cost), 2) drugcost,ROUND(AVG(mdtl.manage_benchmark), 2) AVGFEEStd,min(P00800000) minVal,max(P00800000) maxVal , \n ") ;
		strb.append(" ROUND(AVG(expsum.drg_med_cost),2) med,ROUND(AVG(expsum.drg_nurs_cost),2) nurs,ROUND(AVG(expsum.drg_tech_cost),2) tech, \n ") ;
		strb.append(" ROUND(AVG(expsum.drg_drug_cost),2)  drug,ROUND(AVG(expsum.drg_manage_cost),2)  manage \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code \n ") ;
		strb.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
		strb.append(" and drg.drg_code = :pardrgCode  \n ") ;
		// 标杆值
		strb.append(" union  \n ") ;
		strb.append(" select 4 intervel, 0 incnt,std.avgFee AVGFEE,std.AvgDays  avgdays,std.DrugFee drugcost, std.avgFee AVGFEEStd ,0 minVal,0 maxVal, \n ") ;
		strb.append(" std.med med,std.nurs nurs,std.tech tech,std.DrugMaterFee drug, std.manage manage \n ") ;
		strb.append(" from ( \n ") ;
		strb.append(" select * from (select parquota_code, manage_benchmark from drgs.t_managesummarydtl mdtl \n ") ;
		strb.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.std_year = :manageYear and mdtl.patient_type = '1' and mdtl.pardrg_code = :pardrgCode ) \n ") ;
		strb.append(" pivot (sum(manage_benchmark) for parquota_code \n ") ;
		strb.append(" in ('AvgFee' avgFee , 'AvgDays' AvgDays , 'MedFee' med, 'NursFee' nurs, 'TechFee' tech, 'ManageFee' manage, 'DrugMaterFee' DrugMaterFee,'DrugFee' DrugFee )) \n ") ;
		strb.append(" )std \n ") ;
		String sql = strb.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 and 1=1 \n " ) ;
		}
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " medins.parid is not null \n  and 1=1 " ) ;
		}
		List<Map<String, Object>>  mapList = payGroupApplication.executeQueryForList(sql, dto) ;
		return mapList;
	}
	
	/**
	 * 
	 *  @Description：
	 *  @Author ChenHanlin  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#queryOverCaseFee(com.dhcc.DRGsHosp.facade.dto.IndexCalDTO, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> queryOverCaseFee(IndexCalDTO dto, double sliderVal,String intervel) {
		//double sliderValue = (sliderVal+100)/100 ;
		// 时间格式化
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    //获取分组方案 and  pd.ver_region=:verRegion
	    dto.setVerRegion(drgsIndexCalFacade.getGroupScheme());
	    dto.setMsCode("std_paygroup");
	    dto.setManageYear(drgsIndexCalFacade.getManageYear(dto));
		StringBuffer strb = new StringBuffer() ;
		strb.append(" select dep.dep_code,dep.dep_name,count(ipinfo.pmainindex) cnt,Round(count(ipinfo.pmainindex)/a.cntTotal*100,2) cntR, \n ") ;
		strb.append(" Round(avg(ipinfo.p00800000),2) avgFee,Round(avg(mdtl.manage_benchmark),2) avgFeeStd,Round(sum(ipinfo.p00800000-mdtl.manage_benchmark)/10000,2) overFee, \n ") ;
		strb.append(" Round(avg(expsum.drug_cost),2) drugcost,Round(avg(expsum.drg_nurs_cost),2) nursFee , \n ") ;
		strb.append(" Round(avg(expsum.drg_tech_cost),2)  techFee,Round(avg(expsum.drg_med_cost),2) medFee,Round(avg(expsum.drg_med_cost),2)  medFee, \n ") ;
		strb.append(" Round(avg(expsum.drg_drug_cost),2)  drugMaterFee \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.t_department dep on dep.dep_code = ipinfo.p00300001 \n ") ;
		strb.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" left join ( \n ") ;
		strb.append(" select  count(ipinfo.pmainindex) cntTotal \n ") ;
		strb.append(" from drgs.mr_baseinfo a \n ");
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		strb.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		strb.append(" left join drgs.t_department dep on dep.dep_code = ipinfo.p00300001 \n ") ;
		strb.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
		strb.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
		strb.append(" and pd.pardrg_code = :pardrgCode \n ") ;
		strb.append(" )a on 2=2 \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
		strb.append(" and pd.pardrg_code = :pardrgCode \n ") ;
		if("1".equals(intervel)) {
			strb.append(" and ipinfo.p00800000 >  "+sliderVal+"*mdtl.manage_benchmark \n ") ;
		} else if("2".equals(intervel)){
			strb.append(" and ipinfo.p00800000 <= "+sliderVal+"*mdtl.manage_benchmark and ipinfo.p00800000 > mdtl.manage_benchmark \n ") ;
		} else if("3".equals(intervel)){
			strb.append(" and ipinfo.p00800000 <= mdtl.manage_benchmark \n ") ;
		}
		strb.append(" group by dep.dep_code,dep.dep_name,a.cntTotal \n ") ;
		String sql = strb.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 and 1=1 \n " ) ;
		}
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " medins.parid is not null \n  and 1=1 " ) ;
		}
		List<Map<String, Object>>  mapList = payGroupApplication.executeQueryForList(sql, dto) ;
		return mapList;
	}
	/**
	 * 
	 *  @Description：病组病例明细查询对比表
	 *  @Author Shaozhiyuan-ha  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#queryCostCaseCompare(com.dhcc.DRGsHosp.facade.dto.IndexCalDTO, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> queryCostCaseCompare(IndexCalDTO dto, String sliderVal) {
		// 时间格式化
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    //获取病例区间类型
	    String  intervel=dto.getTypeOne()==null?"0":dto.getTypeOne().toString().trim();
	    //获取是标杆法 1 还是标差法 2
	    String methodType=dto.getTypeTwo()==null?"1":dto.getTypeTwo().toString().trim();
	    //获取分组方案 and  pd.ver_region=:verRegion
	    dto.setVerRegion(drgsIndexCalFacade.getGroupScheme());
	    dto.setMsCode("std_paygroup");
	    dto.setManageYear(drgsIndexCalFacade.getManageYear(dto));
		StringBuffer strszy = new StringBuffer() ;
		
	   if("1".equals(methodType)){
			// 区间病例
			strszy.append(" select '1' lev,a.depname depname, count(1) incnt , ROUND(AVG(P00800000), 2) AVGFEE , ROUND(AVG(a.drugcost), 2) drugcost, round(AVG(a.drugcost)/AVG(P00800000),2)*100 drugcostradio, \n ") ;
			strszy.append(" ROUND(AVG(P00320000), 0) AVGDAYS  ,   \n ") ;
			strszy.append(" ROUND(AVG(a.med), 2) med,ROUND(AVG(a.nurs), 2) nurs,ROUND(AVG(a.tech), 2) tech,ROUND(AVG(a.drug), 2) drug,  \n ") ;
			strszy.append(" ROUND(AVG(a.manage), 2) manage from   \n ") ;
			strszy.append(" (select  dt.dep_name depname,ipinfo.P00300001 depcode,  ipinfo.p00800000, ipinfo.P00320000 ,mdtl.manage_benchmark as_avg_fee , \n ") ;
			strszy.append(" expsum.drg_med_cost med,expsum.drg_nurs_cost nurs,expsum.drg_tech_cost tech, \n ") ;
			strszy.append(" expsum.drg_drug_cost drug,expsum.drg_manage_cost manage,expsum.drug_cost drugcost \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region=:verRegion  \n ") ;
			strszy.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
			strszy.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code  \n ") ;
			strszy.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
			strszy.append(" left join drgs.t_department dt on ipinfo.P00300001=dt.dep_code  \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1  \n ") ;
			if(StringUtils.hasLength(dto.getPardepCode()))
			{
				strszy.append(" and ipinfo.P00300001=:pardepCode   \n ") ;
			}
			strszy.append(" and drg.drg_code = :pardrgCode   \n ") ;
			if("1".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > "+sliderVal+"*mdtl.manage_benchmark \n ") ;
			}else if("2".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > mdtl.manage_benchmark and ipinfo.p00800000 <= "+sliderVal+"*mdtl.manage_benchmark \n ") ;
			}else if("3".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= mdtl.manage_benchmark \n ") ;
			}
			strszy.append(" )a group by  a.depname,a.depcode  \n ") ;
			strszy.append(" union \n ") ;
			// 全部病例
			strszy.append(" select '2' lev,'合计' depname,  count(1) incnt , ROUND(AVG(P00800000), 2) AVGFEE , ROUND(AVG(expsum.drug_cost), 2) drugcost, \n ") ;
			strszy.append(" round(AVG(expsum.drug_cost)/AVG(P00800000),2)*100 drugcostradio,  \n ") ;
			strszy.append(" ROUND(AVG(P00320000), 0) AVGDAYS, ROUND(AVG(expsum.drg_med_cost), 2) med, ROUND(AVG(expsum.drg_nurs_cost), 2) nurs, \n ") ;
			strszy.append(" ROUND(AVG(expsum.drg_tech_cost), 2) tech,ROUND(AVG(expsum.drg_drug_cost), 2) drug,  \n ") ;
			strszy.append(" ROUND(AVG(expsum.drg_manage_cost), 2) manage \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region=:verRegion  \n ") ;
			strszy.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex  \n ") ;
			strszy.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code \n ") ;
			strszy.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
			if(StringUtils.hasLength(dto.getPardepCode()))
			{
				strszy.append(" and ipinfo.P00300001=:pardepCode   \n ") ;
			}
			if("1".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > "+sliderVal+"*mdtl.manage_benchmark \n ") ;
			}else if("2".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > mdtl.manage_benchmark and ipinfo.p00800000 <= "+sliderVal+"*mdtl.manage_benchmark \n ") ;
			}else if("3".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= mdtl.manage_benchmark \n ") ;
			}
			strszy.append(" and drg.drg_code = :pardrgCode \n ") ;
			strszy.append(" union \n ") ;
			// 获取标杆值
			strszy.append(" (select '0' lev,'标杆值' depname, 0 incnt, std.avgFee AVGFEE , std.DrugFee drugcost,  round(std.DrugFee/std.avgFee,2)*100 drugcostradio , \n ") ;
			strszy.append(" std.avgdays  avgdays,  std.med med,std.nurs nurs,std.tech tech,  \n ") ;
			strszy.append(" std.drugmaterfee drug, std.manage manage \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion  \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" left join (\n ") ;
			strszy.append(" select * from (select pardrg_code,parquota_code, manage_benchmark from drgs.t_managesummarydtl mdtl \n ") ;
			strszy.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.std_year = :manageYear and mdtl.patient_type = '1' and mdtl.pardrg_code = :pardrgCode ) \n ") ;
			strszy.append(" pivot (sum(manage_benchmark) for parquota_code \n ") ;
			strszy.append(" in ('AvgFee' avgFee , 'AvgDays' AvgDays , 'MedFee' med, 'NursFee' nurs, 'TechFee' tech, 'ManageFee' manage, 'DrugMaterFee' drugmaterfee,'DrugFee' DrugFee )) \n ") ;
			strszy.append(" )std on pd.pardrg_code = std.pardrg_code  \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1  \n ") ;
			strszy.append(" and pd.pardrg_code = :pardrgCode  ) \n ") ; 
	   }else{
			// 区间病例
			strszy.append(" select '0' lev,a.depname depname, count(1) incnt , ROUND(AVG(P00800000), 2) AVGFEE , ROUND(AVG(a.drugcost), 2) drugcost, round(AVG(a.drugcost)/AVG(P00800000),2)*100 drugcostradio, \n ") ;
			strszy.append(" ROUND(AVG(P00320000), 0) AVGDAYS  ,   \n ") ;
			strszy.append(" ROUND(AVG(a.med), 2) med,ROUND(AVG(a.nurs), 2) nurs,ROUND(AVG(a.tech), 2) tech,ROUND(AVG(a.drug), 2) drug,  \n ") ;
			strszy.append(" ROUND(AVG(a.manage), 2) manage from   \n ") ;
			strszy.append(" (select  dt.dep_name depname,ipinfo.P00300001 depcode,  ipinfo.p00800000, ipinfo.P00320000 ,mdtl.manage_benchmark as_avg_fee , \n ") ;
			strszy.append(" expsum.drg_med_cost med,expsum.drg_nurs_cost nurs,expsum.drg_tech_cost tech, \n ") ;
			strszy.append(" expsum.drg_drug_cost drug,expsum.drg_manage_cost manage,expsum.drug_cost drugcost \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region=:verRegion \n ") ;
			strszy.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex \n ") ;
			strszy.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code  \n ") ;
			strszy.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
			strszy.append(" left join drgs.t_department dt on ipinfo.P00300001=dt.dep_code  \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" left join (select  stddev(ipinfo.p00800000) stddevia \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   and  pd.ver_region=:verRegion \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and pd.pardrg_code = :pardrgCode and 1=1 )stddevia on 2=2 \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1  \n ") ;
			strszy.append(" and drg.drg_code = :pardrgCode   \n ") ;
			if("1".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > "+sliderVal+"*stddevia.stddevia \n ") ;
			}else if("2".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= "+sliderVal+"*stddevia.stddevia and ipinfo.p00800000 > stddevia.stddevia \n ") ;
			}else if("3".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= stddevia.stddevia \n ") ;
			}
			strszy.append(" )a group by  a.depname,a.depcode  \n ") ;
			strszy.append(" union \n ") ;
			  // 全部病例
			strszy.append(" select '2' lev,'合计' depname,  count(1) incnt , ROUND(AVG(P00800000), 2) AVGFEE , ROUND(AVG(expsum.drug_cost), 2) drugcost, \n ") ;
			strszy.append(" round(AVG(expsum.drug_cost)/AVG(P00800000),2)*100 drugcostradio,  \n ") ;
			strszy.append(" ROUND(AVG(P00320000), 0) AVGDAYS, ROUND(AVG(expsum.drg_med_cost), 2) med, ROUND(AVG(expsum.drg_nurs_cost), 2) nurs, \n ") ;
			strszy.append(" ROUND(AVG(expsum.drg_tech_cost), 2) tech,ROUND(AVG(expsum.drg_drug_cost), 2) drug,  \n ") ;
			strszy.append(" ROUND(AVG(expsum.drg_manage_cost), 2) manage \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region=:verRegion \n ") ;
			strszy.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex  \n ") ;
			strszy.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code \n ") ;
			//strszy.append(" left join drgs.t_managesummarydtl mdtl on mdtl.par_ms_code = 'std_paygroup' and mdtl.pardrg_code = pd.pardrg_code and mdtl.parquota_code = 'AvgFee' and mdtl.std_year = :manageYear \n ") ;
			strszy.append(" left join (select  stddev(ipinfo.p00800000) stddevia \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   and  pd.ver_region=:verRegion \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and pd.pardrg_code = :pardrgCode and 1=1 )stddevia on 2=2 \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1  \n ") ;
			strszy.append(" and drg.drg_code = :pardrgCode \n ") ;
			if("1".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > "+sliderVal+"*stddevia.stddevia \n ") ;
			}else if("2".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= "+sliderVal+"*stddevia.stddevia and ipinfo.p00800000 > stddevia.stddevia \n ") ;
			}else if("3".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= stddevia.stddevia \n ") ;
			}
			strszy.append(" union \n ") ;
			// 获取标杆值
			strszy.append(" (select '0' lev,'标杆值' depname, 0 incnt, std.avgFee AVGFEE , std.DrugFee drugcost,  round(std.DrugFee/std.avgFee,2)*100 drugcostradio , \n ") ;
			strszy.append(" std.avgdays  avgdays,  std.med med,std.nurs nurs,std.tech tech,  \n ") ;
			strszy.append(" std.drugmaterfee drug, std.manage manage \n ") ;
			strszy.append(" from drgs.mr_baseinfo a \n ");
			strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
			strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region=:verRegion  \n ") ;
			strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
			strszy.append(" left join (\n ") ;
			strszy.append(" select * from (select pardrg_code,parquota_code, manage_benchmark from drgs.t_managesummarydtl mdtl \n ") ;
			strszy.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.std_year = :manageYear and mdtl.patient_type = '1' and mdtl.pardrg_code = :pardrgCode ) \n ") ;
			strszy.append(" pivot (sum(manage_benchmark) for parquota_code \n ") ;
			strszy.append(" in ('AvgFee' avgFee , 'AvgDays' AvgDays , 'MedFee' med, 'NursFee' nurs, 'TechFee' tech, 'ManageFee' manage, 'DrugMaterFee' drugmaterfee,'DrugFee' DrugFee )) \n ") ;
			strszy.append(" )std on pd.pardrg_code = std.pardrg_code  \n ") ;
			strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1  \n ") ;
			strszy.append(" and pd.pardrg_code = :pardrgCode  ) \n ") ; 
			
	   }
	    String sql = strszy.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 and 1=1 \n " ) ;
		}
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " medins.parid is not null \n  and 1=1 " ) ;
		}
		List<Map<String, Object>>  mapList = payGroupApplication.executeQueryForList(sql, dto) ;
		return mapList;
	}
	/**
	 * 
	 *  @Description：病组病例明细查询详细表
	 *  @Author Shaozhiyuan-ha  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#queryCostOutCaseList(com.dhcc.DRGsHosp.facade.dto.IndexCalDTO, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> queryCostOutCaseList(IndexCalDTO dto, String sliderVal) {
		// 时间格式化
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    //获取病例区间类型
	    String  intervel=dto.getTypeOne()==null?"0":dto.getTypeOne().toString().trim();
	    //获取是标杆法 1 还是标差法 2
	    String methodType=dto.getTypeTwo()==null?"1":dto.getTypeTwo().toString().trim();
	    //获取科室
	    String depName=dto.getTypeThree()==null?"all":dto.getTypeThree().toString().trim();
	    //获取分组方案 and  pd.ver_region=:verRegion
	    dto.setVerRegion(drgsIndexCalFacade.getGroupScheme());
	    dto.setMsCode("std_paygroup");
	    dto.setManageYear(drgsIndexCalFacade.getManageYear(dto));
		StringBuffer strszy = new StringBuffer() ;
		
	   if("1".equals(methodType)){
		   strszy.append(" select a.PMAININDEX PMAININDEX, a.P00050000 MRNO, a.P00060000 NAME, a.P00070000 GENDER, a.P00090000 AGE, to_char(ipinfo.P00250000,'yyyy-mm-dd') INDATE, to_char(ipinfo.P00290000,'yyyy-mm-dd')  OUTDATE , ipinfo.P00300000 OUTDEP,  \n ") ; 
		   strszy.append(" ipinfo.P00600300 DR, diag.pd0101||'-'||diag.pd0100 diag , oper.odesc oper, ipinfo.P00800000 cost , std.avgfee  costf,  \n ") ;
		   strszy.append(" ipinfo.P00320000 indays , std.avgdays indaysf , expsum.DRG_MED_COST med, std.med medf , expsum.DRG_NURS_COST nurs, std.nurs nursf ,   \n ") ;
		   strszy.append(" expsum.DRG_MANAGE_COST manage  ,std.manage managef , expsum.DRG_TECH_COST tech , std.tech techf,  expsum.DRG_DRUG_COST drug , std.DrugMaterFee drugf  \n ") ;
		   strszy.append(" from drgs.mr_baseinfo a \n ");
		   strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		   strszy.append(" left join drgs.mr_diagnose diag on ipinfo.pmainindex=diag.pmainindex and diag.pdtype='M'  \n ") ;
		   strszy.append(" left join (  select operation.pmainindex pmainindex,replace(wm_concat(operation.po0101||'('||operation.po0100||')'),',','/')   odesc  from \n ") ;
		   strszy.append(" (select * from drgs.Mr_Operation operation where operation.potype='M' order by operation.po1400 desc ) operation \n ") ;
		   strszy.append(" group by operation.pmainindex  \n ") ;
		   strszy.append(" )oper on oper.pmainindex = ipinfo.pmainindex  \n ") ;
		   strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		   strszy.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex  and  pd.ver_region=:verRegion \n ") ;
		   strszy.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code  \n ") ;
		   strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		   strszy.append(" left join ( \n ") ;
		   strszy.append(" select * from (select pardrg_code,parquota_code, manage_benchmark from drgs.t_managesummarydtl mdtl \n ") ;
		   strszy.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.std_year = :manageYear and mdtl.patient_type = '1' ) \n ") ;
		   strszy.append(" pivot (sum(manage_benchmark) for parquota_code \n ") ;
		   strszy.append(" in ('AvgFee' avgfee , 'AvgDays' avgdays , 'MedFee' med, 'NursFee' nurs, 'TechFee' tech, 'ManageFee' manage, 'DrugMaterFee' DrugMaterFee,'DrugFee' DrugFee )) \n ") ;
		   strszy.append(" )std on std.pardrg_code = pd.pardrg_code \n ") ;
		   strszy.append(" left join drgs.t_department dt on ipinfo.P00300001=dt.dep_code  \n ") ;
		   strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1  \n ") ;
		   strszy.append(" and drg.drg_code = :pardrgCode   \n ") ;
			if(StringUtils.hasLength(dto.getPardepCode()))
			{
				strszy.append(" and ipinfo.P00300001=:pardepCode   \n ") ;
			}
		   //判断在哪个区间条件里
		    if("1".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > "+sliderVal+"*std.avgfee \n ") ;
			}else if("2".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > std.avgfee and ipinfo.p00800000 <= "+sliderVal+"*std.avgfee \n ") ;
			}else if("3".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= std.avgfee \n ") ;
			}
		   //判断在哪个科室下
		   if(!"all".equals(depName)){
			   strszy.append(" and ipinfo.P00300000= :typeThree \n ") ;
		   }
	   }else{
		   strszy.append(" select a.PMAININDEX PMAININDEX, a.P00050000 MRNO, a.P00060000 NAME, a.P00070000 GENDER, a.P00090000 AGE, to_char(ipinfo.P00250000,'yyyy-mm-dd') INDATE, to_char(ipinfo.P00290000,'yyyy-mm-dd')  OUTDATE , ipinfo.P00300000 OUTDEP,  \n ") ; 
		   strszy.append(" ipinfo.P00600300 DR, diag.pd0101||'-'||diag.pd0100 diag , oper.odesc oper, ipinfo.P00800000 cost , std.avgfee  costf,  \n ") ;
		   strszy.append(" ipinfo.P00320000 indays ,  std.avgdays indaysf ,   expsum.DRG_MED_COST med, std.med medf , expsum.DRG_NURS_COST nurs, std.nurs nursf ,   \n ") ;
		   strszy.append(" expsum.DRG_MANAGE_COST manage  ,std.manage managef , expsum.DRG_TECH_COST tech , std.tech techf,  expsum.DRG_DRUG_COST drug , std.drugmaterfee drugf  \n ") ;
		   strszy.append(" from drgs.mr_baseinfo a \n ");
		   strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		   strszy.append(" left join drgs.mr_diagnose diag on ipinfo.pmainindex=diag.pmainindex and diag.pdtype='M'  \n ") ;
		   strszy.append(" left join (  select operation.pmainindex pmainindex,replace(wm_concat(operation.po0101||'('||operation.po0100||')'),',','/')   odesc  from \n ") ;
		   strszy.append(" (select * from drgs.Mr_Operation operation where operation.potype='M' order by operation.po1400 desc ) operation \n ") ;
		   strszy.append(" group by operation.pmainindex  \n ") ;
		   strszy.append(" )oper on oper.pmainindex = ipinfo.pmainindex  \n ") ;		   strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   and  pd.ver_region=:verRegion \n ") ;
		   strszy.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex  \n ") ;
		   strszy.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code  \n ") ;
		   strszy.append(" left join drgs.t_department dt on ipinfo.P00300001=dt.dep_code  \n ") ;
		   strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		   strszy.append(" left join ( \n ") ;
		   strszy.append(" select * from (select pardrg_code,parquota_code, manage_benchmark from drgs.t_managesummarydtl mdtl \n ") ;
		   strszy.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.std_year = :manageYear and mdtl.patient_type = '1' ) \n ") ;
		   strszy.append(" pivot (sum(manage_benchmark) for parquota_code \n ") ;
		   strszy.append(" in ('AvgFee' avgfee , 'AvgDays' avgdays , 'MedFee' med, 'NursFee' nurs, 'TechFee' tech, 'ManageFee' manage, 'DrugMaterFee' DrugMaterFee,'DrugFee' DrugFee )) \n ") ;
		   strszy.append(" )std on std.pardrg_code = pd.pardrg_code \n ") ;
		   strszy.append(" left join (select  stddev(ipinfo.p00800000) stddevia \n ") ;
		   strszy.append(" from drgs.mr_baseinfo a \n ");
		   strszy.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
		   strszy.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  and  pd.ver_region=:verRegion \n ") ;
		   strszy.append(" left join drgs.t_medins medins on medins.parid = ipinfo.pmainindex \n ") ;
		   strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and pd.pardrg_code = :pardrgCode and 1=1 )stddevia on 2=2 \n ") ;
		   strszy.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and 1=1 \n ") ;
		   strszy.append(" and drg.drg_code = :pardrgCode  \n ") ;
		   //判断在哪个区间条件里
		    if("1".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > "+sliderVal+"*stddevia.stddevia \n ") ;
			}else if("2".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 > stddevia.stddevia and ipinfo.p00800000 <= "+sliderVal+"*stddevia.stddevia \n ") ;
			}else if("3".equals(intervel)){
				strszy.append(" and ipinfo.p00800000 <= stddevia.stddevia \n ") ;
			} 
		   //判断在哪个科室下
		   if(!"all".equals(depName)){
			   strszy.append(" and ipinfo.P00300000= :typeThree \n ") ;
		   }
//		   strszy.append(" group by  \n ") ;
	   }
	    String sql = strszy.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 and 1=1 \n " ) ;
		}
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " medins.parid is not null \n  and 1=1 " ) ;
		}
		List<Map<String, Object>>  mapList = payGroupApplication.executeQueryForList(sql, dto) ;
		return mapList;
	}
	/**
	 * 
	 *  @Description：病例分布特征
	 *  @Author WENYI  
	 *  @see com.dhcc.DRGsHosp.facade.PayGroupFacade#getNormalDistributionData(com.dhcc.DRGsHosp.facade.dto.IndexCalDTO, java.lang.String, int, java.lang.String)
	 */
	public Map<String, Object> getNormalDistributionData(IndexCalDTO dto, String normalParam,int interval,String queryType){
		Map<String, Object> result=new HashMap<>();
		//根据条件求标准差和平均值
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    Map<String, Object> paramMap=payGroupApplication.getNormalParamData(IndexCalAssembler.toEntity(dto), normalParam);
		double avg=0;
		double std=0;
		if(paramMap.get("AVG")!=null){
			avg=((BigDecimal)paramMap.get("AVG")).doubleValue();
		}
		if(paramMap.get("STD")!=null){
			std=((BigDecimal)paramMap.get("STD")).doubleValue();
		}
	    //查病例费用数据
	    List<Object[]> aList=payGroupApplication.getNormalCaseData(IndexCalAssembler.toEntity(dto));
		//划分区间，求指标值
	    if(aList.size()>0){
	    	result=getChartData(aList,interval,avg,std,queryType);
	    }
	    return result;
	}
	/**
	 * 
	 *  getChartData.  
	 *  @Description：获取柱状图数据
	 *  @Author WENYI
	 *  @param aList
	 *  @param intervalSize
	 *  @param avg
	 *  @param std
	 *  @param queryType
	 *  @return 
	 *  @Date:2017年9月4日上午10:37:02
	 */
	private Map<String, Object> getChartData(List<Object[]> aList,int intervalSize,double avg,double std,String queryType){
		Map<String, Object> result=new HashMap<>();
		DecimalFormat dfTwo=new DecimalFormat("#.##");
		int aSize=aList.size();
		int intervalFrequency[]=new int[intervalSize];
		String category[]=new String[intervalSize+1];
		
		//宽度
		double minNumber=((BigDecimal)aList.get(0)[0]).doubleValue();
		double maxNumber=((BigDecimal)aList.get(aSize-1)[0]).doubleValue();
		double range=Math.ceil((maxNumber-minNumber)/intervalSize);//区间距离
		
		Map<String,Object> depMap=new HashMap<>();
		
		//频次
		for(int i=0;i<aSize;i++){
			int index=(int)Math.floor((((BigDecimal)aList.get(i)[0]).doubleValue()-minNumber)/range);//计算当前费用值在哪个区间
			intervalFrequency[index]=intervalFrequency[index]+1;//频数加1
			
			if("depDrg".equals(queryType)){
				String depCode=aList.get(i)[1].toString();
				if(depMap.containsKey(depCode)){
					Map<String,Object> tempMap=(Map<String, Object>) depMap.get(depCode);
					List<Integer> frequencyList=(List<Integer>) tempMap.get("frequency");
					frequencyList.set(index, frequencyList.get(index)==null?1:frequencyList.get(index)+1);
					tempMap.put("frequency", frequencyList);
					depMap.put(depCode, tempMap);
				}
				else{
					Map<String,Object> tempMap=new HashMap<>();
					Integer tempArray[]=new Integer[intervalSize];
					tempArray[index]=1;
					tempMap.put("depCode", depCode);
					tempMap.put("depDesc", aList.get(i)[2]==null?"":aList.get(i)[2].toString());
					tempMap.put("frequency", Arrays.asList(tempArray));
					depMap.put(depCode, tempMap);
				}
			}
		}
		
		//概率密度
		double probilityDensity[]=new double[intervalSize];
		//累计分布
		String cumulativeDistribution[]=new String[intervalSize];
		double minCost=((BigDecimal)aList.get(0)[0]).doubleValue();//住院费用最小值
		for(int i=0;i<intervalSize;i++){
			//类目轴数据
			category[i]=dfTwo.format(minCost+i*range);
			//当前区间的上限
			double tempMax=minCost+((i+1)*range);
			//概率密度
			probilityDensity[i]=Math.pow(Math.E, -Math.pow(tempMax-avg, 2)/(2*std*std))/(std*Math.sqrt(2*Math.PI));
			//累计分布
			cumulativeDistribution[i]=dfTwo.format(100*simpsonRule(0,tempMax,100,std,avg)/(std*Math.sqrt(2*Math.PI)));
		}
		category[intervalSize]=dfTwo.format(minCost+intervalSize*range);
		result.put("category", category);
		if("depDrg".equals(queryType)){
			result.put("intervalFrequency", depMap.values());
			result.put("frequency", intervalFrequency);
		}
		else{
			result.put("intervalFrequency", intervalFrequency);
		}
		result.put("cumulativeDistribution", cumulativeDistribution);
		result.put("probilityDensity", probilityDensity);
		return result;
	}
	/**
	 * 
	 *  simpsonRule.  
	 *  @Description：定积分的近似计算--辛普森法
	 *  @Author WENYI
	 *  @param lower
	 *  @param upper
	 *  @param n
	 *  @param std
	 *  @param avg
	 *  @return 
	 *  @Date:2017年9月4日上午10:37:16
	 */
	public double simpsonRule(double lower, double upper, int n,double std,double avg) {  
        double result = 0;  
        double unit = (upper-lower)/n;  
        double factor = unit / 3;  
        double[] x = new double[n+1];  
          
        for (int i = 0; i < x.length; i++) {  
            x[i] = lower + unit*i;  
        }  
        for (int i = 0; i < x.length; i++) {  
            if(i==0 || i==x.length-1) { 
            	result+=Math.pow(Math.E, -Math.pow(x[i]-avg, 2)/(2*std*std));
            }else if(i%2 == 0) { // if i is even num.  
                result += 2*Math.pow(Math.E, -Math.pow(x[i]-avg, 2)/(2*std*std));  
            }else { // if i is odd num.    
                result += 4*Math.pow(Math.E, -Math.pow(x[i]-avg, 2)/(2*std*std));  
            }  
        }                 
          
        result *= factor;  
        return result;  
    }
	
	@Override
	public List<Map<String, Object>> findPayGroupDetails(IndexCalDTO dto) {
		// 时间格式化
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
	    dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
	    // 获取标杆
	    dto.setMsCode("std_paygroup");
	    dto.setManageYear(drgsIndexCalFacade.getManageYear(dto));
		StringBuffer strb = new StringBuffer() ;
		strb.append(" select a.*,b.casecnt,a.ExcessCnt_F/a.paygroupCaseCnt ExcessCnt_FR,a.drugFee/a.avgFee drugFeeR,a.materFee/a.avgFee materFeeR,paygroupCaseCnt/b.casecnt paygroupCaseCntR \n ") ;
		strb.append(" from ( \n ") ;
		strb.append(" select drg.drg_desc ddesc, drg.drg_code ccode, \n ") ;
		// -- 付费病组病例数
		strb.append(" count(1) paygroupCaseCnt, \n ") ;
		// -- 费用超标病例数
		strb.append(" sum(case when ipinfo.p00800000>std.AvgFeeStd then 1 else 0 end) ExcessCnt_F , \n ") ;
		strb.append(" avg(ipinfo.p00800000) avgFee , \n ") ;
		strb.append(" avg(ipinfo.p00320000) avgDays , \n ") ;
		strb.append(" std.AvgFeeStd , \n ") ;
		strb.append(" avg(expsum.drug_cost) as drugFee , avg(expsum.mater_cost) as materFee , \n ") ;
		strb.append(" avg(expsum.drg_med_cost) as MedFee , avg(expsum.drg_nurs_cost) as nursFee , \n ") ;
		strb.append(" avg(expsum.drg_tech_cost) as techFee , avg(expsum.drg_drug_cost) as drugMaterFee , \n ") ;
		strb.append(" avg(expsum.drg_manage_cost) as manageFee \n ") ;
		strb.append(" from DRGs.Mr_Baseinfo a  \n ") ;
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex=a.pmainindex \n ") ;
		strb.append(" left join DRGs.t_patientdrg b on a.pmainindex=b.parid \n ") ;
		strb.append(" left join drgs.t_drgs drg on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE \n ") ;
		strb.append(" left join drgs.t_adrg adrg on drg.adrg_code = adrg.adrg_code and drg.ver_code = adrg.ver_code \n ") ;
		strb.append(" left join drgs.t_mdcgroup mdc on adrg.mdc_code = mdc.mdc_code and adrg.ver_code = mdc.ver_code \n ") ;
		strb.append(" left join drgs.t_department dep on dep.dep_code = ipinfo.p00300001 \n ") ;
		strb.append(" left join DRGs.t_medins d on d.parid=a.pmainindex \n ") ;
		strb.append(" left join ( \n ") ;
		strb.append(" select * from (select mdtl.pardrg_code drgcode,mdtl.parquota_code quotacode,mdtl.manage_benchmark benchmark  from drgs.t_managesummarydtl mdtl \n ") ;
		strb.append(" where mdtl.par_ms_code = 'std_paygroup' and mdtl.patient_type = '1' and mdtl.std_year = :manageYear ) \n ") ;
		strb.append(" pivot (sum(benchmark) for quotacode in ('AvgFee' AvgFeeStd ,'AvgDays' AvgDayStd))  \n ") ;
		strb.append(" )std on std.drgcode = drg.drg_code \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo  \n ") ;
		strb.append(" and  d.parid is not null and b.pardrg_code is not null and ipinfo.p00320000<61 and drg.pay_group = '1' and 1=1 \n ") ;
		strb.append(" group by drg_desc, drg.drg_code,std.AvgFeeStd \n ") ;
		strb.append(" ) a,(select count(1) casecnt \n ") ;
		strb.append(" from DRGs.Mr_Baseinfo a  \n ") ;
		strb.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ") ;
		strb.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex=a.pmainindex \n ") ;
		strb.append(" left join DRGs.t_patientdrg b on a.pmainindex=b.parid \n ") ;
		strb.append(" left join drgs.t_drgs drg on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE \n ") ;
		strb.append(" left join drgs.t_adrg adrg on drg.adrg_code = adrg.adrg_code and drg.ver_code = adrg.ver_code \n ") ;
		strb.append(" left join drgs.t_mdcgroup mdc on adrg.mdc_code = mdc.mdc_code and adrg.ver_code = mdc.ver_code \n ") ;
		strb.append(" left join drgs.t_department dep on dep.dep_code = ipinfo.p00300001 \n ") ;
		strb.append(" left join drgs.t_medins d on d.parid=a.pmainindex \n ") ;
		strb.append(" where to_char(ipinfo.p00290000,'yyyy-mm-dd') between :dateFrom and :dateTo and  d.parid is not null and b.pardrg_code is not null and ipinfo.p00320000<61 and drg.pay_group = '1' \n and 1=1 ) b \n ") ;
		if("PayGroupOverFeeDrgCnt".equals(dto.getQuotaCode())) {
			strb.append(" where a.avgfee>a.AvgFeeStd \n ") ;
		}
		strb.append(" order by a.ccode \n ") ;
		String sql = strb.toString() ;
		//***医院编码***
		if(StringUtils.hasLength(dto.getHospCode())){
			sql = sql.replaceAll("1=1", " instr('"+dto.getHospCode()+"',a.p00010001)>0 \n and 1=1 " ) ;
		}
		
		// 病组条件
		if(StringUtils.hasLength(dto.getParmdcCode())){
			sql = sql.replaceAll("1=1", " mdc.mdc_code=:parmdcCode \n  and 1=1 " ) ;
		}
		
		// 科室条件
		if(StringUtils.hasLength(dto.getPardepCode())){
			sql = sql.replaceAll("1=1", " dep.dep_code = :pardepCode \n  and 1=1 " ) ;
		}
		
		// 医保条件
		if("1".equals(dto.getpType())){
			sql = sql.replaceAll("1=1", " d.parid is not null \n  and 1=1 " ) ;
		}
		return payGroupApplication.executeQueryForList(sql, dto);
	}

	/**
	 * @param dto
	 * @return
	 * @throws
	 * @Description: 查询分组结果使用的病组
	 * @author chenhanlin
	 * @date 2018/9/4 15:51
	 */
	@Override
	public List<Map<String, Object>> findAllDRG(IndexCalDTO dto) {
		dto.setDateFrom(DrgsIndex.formateDateFrom(dto.getDateFrom()));
		dto.setDateTo(DrgsIndex.formateDateTo(dto.getDateTo()));
		String verCode = drgsIndexCalFacade.getVerCode(dto.getDateFrom(),dto.getDateTo()) ;
		String sql = "select t.drg_code \"ccode\",t.drg_desc \"ddesc\",t.ver_code \"verCode\" from drgs.T_DRGS t where t.ver_code = :verCode \n " ;
		if ("1".equals(dto.getIsPayGroup())) {
			sql+=" and t.pay_group = :isPayGroup " ;
		}
		Map<String,Object> condition = new HashMap<>() ;
		condition.put("verCode",verCode) ;
		condition.put("isPayGroup",dto.getIsPayGroup()) ;
		try {
			List<Map<String, Object>> resultList = drgsIndexCalApplication.excuteQueryForList(sql,condition) ;
			return resultList;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<>();
		}
	}
}
